<?php
/**
 * 用于批量创建学校的交易列表。后台操作。具有安全隐患。
 */
class School_model extends Model{

    function School_model(){
        parent::Model();
        $this->load->database();  //加载数据库
    }

    function create_alltables(){
       $sql="SELECT * from school";
       $query = $this->db->query($sql);
       if( ($query->row_array())==null ){
            return 0;
        }
        else{
            $result=$query->result();
            foreach($result as $school){
                $initial=$school->initials;
                $sql1="CREATE TABLE IF NOT EXISTS `zsaleposts_$initial` (
                  `book_id` int(11) NOT NULL,
                  `user_id` varchar(20) NOT NULL,
                  `book_title` varchar(50) NOT NULL,
                  `num` tinyint(2) NOT NULL DEFAULT '1',
                  `user_name` varchar(20) NOT NULL,
                  `phone` varchar(15) NOT NULL,
                  `email` varchar(30) NOT NULL,
                  `qq` varchar(20) NOT NULL,
                  `price` float NOT NULL,
                  `quality` tinyint(1) NOT NULL DEFAULT '3',
                  `school_id` int(11) NOT NULL,
                  `school_name` int(11) NOT NULL,
                  `campus_id` int(11) NOT NULL,
                  `campus_name` int(11) NOT NULL,
                  `postdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  `expiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `top_expiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `other` varchar(255) DEFAULT '',
                  `dealed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 for not dealed,1 for dealed',
                  PRIMARY KEY (`book_id`,`user_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
                ";
                $query1= $this->db->query($sql1);

                 $sql2="CREATE TABLE IF NOT EXISTS `zwantedposts_$initial` (
                      `book_id` int(11) NOT NULL,
                      `user_id` varchar(20) CHARACTER SET utf8 NOT NULL,
                      `book_title` varchar(50) CHARACTER SET utf8 NOT NULL,
                      `num` int(5) NOT NULL DEFAULT '1',
                      `user_name` varchar(20) CHARACTER SET utf8 NOT NULL,
                      `phone` varchar(15) CHARACTER SET utf8 NOT NULL,
                      `email` varchar(30) CHARACTER SET utf8 NOT NULL,
                      `qq` varchar(20) CHARACTER SET utf8 NOT NULL,
                      `start_price` int(11) NOT NULL,
                      `end_price` float NOT NULL,
                      `school_id` int(11) NOT NULL,
                      `school_name` int(11) NOT NULL,
                      `campus_id` int(11) NOT NULL,
                      `campus_name` int(11) NOT NULL,
                      `postdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                      `expiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                      `top_expiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                      `other` varchar(255) CHARACTER SET utf8 DEFAULT '',
                      `dealed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 for not dealed,1 for dealed',
                      PRIMARY KEY (`book_id`,`user_id`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                    $query2= $this->db->query($sql2);
            }
            return 1;
        }
    }

    /*
     * This function is used to attach school id to ip address in the database
     */
    function attach_schoolid_to_ip(){
       $sql="SELECT * from school";
       $query = $this->db->query($sql);
       if( ($query->row_array())==null ){
            return 0;
        }
        else{
            $result=$query->result();
            foreach($result as $school){
                 echo "$school->name";
                 $sql2="UPDATE ip_addrs SET school_id='{$school->id}',school_name='{$school->name}',
                            school_initials='{$school->initials}', city='{$school->city}', province='{$school->province}'
                            WHERE country LIKE '{$school->name}%' OR addr LIKE '{$school->name}%'";
                 //这句话会有一定的误判，比如把浙江大学宁波理工学院归为浙江大学，但是大体效果是好的，因为IP地址数据库不规范，
                 //其中很多数据都是学校后缀一个学院的形式，所以这样的处理方式也就是最优的结果了
                 $query2 = $this->db->query($sql2);                
            } //end  of foreach school
        }
    }
    
   function choose_school_byip($ip_num){
       $sql="select * from ip_addrs where ip_begin <='{$ip_num}' and ip_end >='{$ip_num}' and school_id<>0 ";
       $query = $this->db->query($sql);
       if( ($query->row_array())==null ){
            return 0;
        }
        else
           return $query->result();
   }


}
?>
